﻿using System;
using System.Collections.Generic;
using System.Collections;

using System.Web;
using System.IO;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DB
/// </summary>
public class DB2
{
	public DB2()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    private static SqlConnection OpenConnection()
    {
        SqlConnection conn = null;
        try
        {
            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=usuarios;Integrated Security=True;Pooling=False");
            conn.Open();
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        return conn;
    }

    public static bool ActivateUser(string UserID) {
        bool result = false;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "update tbl_usuario set usu_estado=1 where usu_id=@usuID";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = UserID.Trim().ToUpper();
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            comm.CommandText = "update tbl_usuario_perfil set upe_estado=1 where upe_usu_id=@usuID";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = UserID.Trim().ToUpper();
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();

        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_User ValidaCliente(String user, String pss, String app, string perfil)
    {
        RE_User result = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            if (perfil.Equals("-1")) {
                comm.CommandText = "SELECT usu_id, usu_nombre, usu_apellido, usu_compania, per_nombre, usu_logo, usu_alias, usu_where, usu_where2, usu_permiso, usu_restricted,sum(ape_permiso) fROM tbl_usuario, tbl_usuario_perfil, tbl_perfil_aplicacion, tbl_perfil WHERE per_id=upe_per_id and (upper(usu_id) = @usuID) AND (usu_contrasena = @usuContrasena) and usu_estado=1 and upe_estado=1 and upe_usu_id=usu_id and upe_per_id=ape_per_id and ape_apl_id=1 and ape_per_id in (select per_id from tbl_perfil where per_catalogo=1) group by usu_id, usu_nombre, usu_apellido, usu_compania, per_nombre, usu_logo, usu_alias, usu_where, usu_where2, usu_permiso, usu_restricted";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = user.Trim().ToUpper();
                comm.Parameters.Add("@usuContrasena", SqlDbType.VarChar).Value = pss.Trim();
                comm.Parameters.Add("@aplicacion", SqlDbType.VarChar).Value = app.Trim();
            } else {
                comm.CommandText = "SELECT usu_id, usu_nombre, usu_apellido, usu_compania, per_nombre, usu_logo, usu_alias, , usu_where, usu_where2, usu_permiso, usu_restricted,sum(ape_permiso) fROM tbl_usuario, tbl_usuario_perfil, tbl_perfil_aplicacion, tbl_perfil WHERE per_id=upe_per_id and (upper(usu_id) = @usuID) AND (usu_contrasena = @usuContrasena) and usu_estado=1 and upe_estado=1 and upe_usu_id=usu_id and upe_per_id=ape_per_id and ape_apl_id=1 and ape_per_id=@perfilID group by usu_id, usu_nombre, usu_apellido, usu_compania, per_nombre, usu_logo, usu_alias, usu_where, usu_where2, usu_permiso, usu_restricted";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = user.Trim().ToUpper();
                comm.Parameters.Add("@usuContrasena", SqlDbType.VarChar).Value = pss.Trim();
                comm.Parameters.Add("@aplicacion", SqlDbType.VarChar).Value = app.Trim();
                comm.Parameters.Add("@perfilID", SqlDbType.VarChar).Value = perfil.Trim();
            }
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_User();
                if (!reader.IsDBNull(0)) result.userID = reader.GetString(0);
                if (!reader.IsDBNull(1)) result.nombre = reader.GetString(1);
                if (!reader.IsDBNull(2)) result.apellido = reader.GetString(2);
                if (!reader.IsDBNull(3)) result.compania = reader.GetString(3);
                if (!reader.IsDBNull(4)) result.perfil = reader.GetString(4);
                if (!reader.IsDBNull(5)) result.logo = reader.GetString(5);
                if (!reader.IsDBNull(6)) result.alias = reader.GetString(6);
                if (!reader.IsDBNull(7)) result.where = reader.GetString(7);
                if (!reader.IsDBNull(8)) result.where2 = reader.GetString(8);
                if (!reader.IsDBNull(9)) result.permisouser = reader.GetInt32(9);
                if (!reader.IsDBNull(10)) result.restricted = reader.GetInt32(10);
                if (!reader.IsDBNull(11)) result.permiso = long.Parse(reader.GetValue(11).ToString());
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static bool InsertarCliente(RE_User usuario)
    {
        bool result = false;
        SqlTransaction transaction;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        transaction = conn.BeginTransaction();
        try
        {
            
            
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.Transaction = transaction;
            if (usuario.existe==0)
            {
                comm.CommandText = "insert into tbl_usuario (usu_id, usu_contrasena, usu_estado, usu_fecha_creacion, usu_alias, usu_nombre, usu_apellido,usu_compania, usu_email, usu_title, usu_logo) values (@usuID,@pass, 1, GETDATE(), @alias, @nombre, @apellido, @compania, @email,@title, @logo)";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                comm.Parameters.Add("@pass", SqlDbType.VarChar).Value = usuario.pss;
                comm.Parameters.Add("@alias", SqlDbType.VarChar).Value = usuario.alias.ToUpper();
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = usuario.nombre.ToUpper();
                comm.Parameters.Add("@apellido", SqlDbType.VarChar).Value = usuario.apellido.ToUpper();
                comm.Parameters.Add("@compania", SqlDbType.VarChar).Value = usuario.compania.ToUpper();
                comm.Parameters.Add("@email", SqlDbType.VarChar).Value = usuario.email;
                comm.Parameters.Add("@title", SqlDbType.VarChar).Value = usuario.title.ToUpper();
                comm.Parameters.Add("@logo", SqlDbType.VarChar).Value = usuario.logo.ToUpper();

                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_usuario_perfil (upe_usu_id, upe_per_id, upe_estado) values (@usuID, @perID, 1)";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                comm.Parameters.Add("@perID", SqlDbType.VarChar).Value = int.Parse(usuario.perfil);
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                int uinID = 1;
                int tcid = 1;
                foreach (RE_Direccion direccion in usuario.direcciones)
                {
                    comm.CommandText = "select max(uin_id) from tbl_usuario_informacion";
                    reader = comm.ExecuteReader();
                    if (reader.Read())
                        if (reader.IsDBNull(0)) uinID = 1; else uinID = reader.GetInt32(0) + 1;
                    if (reader != null)
                    {
                        reader.Close();
                        reader.Dispose();
                    }
                    comm.CommandText = "insert into tbl_usuario_informacion(uin_id, uin_usu_id, uin_st_number, uin_st_add, uin_city, uin_state, uin_zip, uin_tel_oficina, uin_tel_celular, uin_fax, uin_tii_id) values (@uinID, @usuID, @stnum, @stadd, @city, @state, @zip, @telof, @telcel, @fax, @tipo)";
                    comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                    comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                    comm.Parameters.Add("@stnum", SqlDbType.VarChar).Value = direccion.stnumber.ToUpper();
                    comm.Parameters.Add("@stadd", SqlDbType.VarChar).Value = direccion.stadd.ToUpper();
                    comm.Parameters.Add("@city", SqlDbType.VarChar).Value = direccion.city.ToUpper();
                    comm.Parameters.Add("@state", SqlDbType.VarChar).Value = direccion.state.ToUpper();
                    comm.Parameters.Add("@zip", SqlDbType.VarChar).Value = direccion.zip;
                    comm.Parameters.Add("@telof", SqlDbType.VarChar).Value = direccion.telof;
                    comm.Parameters.Add("@telcel", SqlDbType.VarChar).Value = direccion.telcel;
                    comm.Parameters.Add("@fax", SqlDbType.VarChar).Value = direccion.fax;
                    comm.Parameters.Add("@tipo", SqlDbType.Int).Value = direccion.tipodir;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();

                    foreach (RE_CreditCard tarjeta in direccion.tarjetas)
                    {

                        comm.CommandText = "select max(tac_id) from tbl_tarjetas_credito";
                        reader = comm.ExecuteReader();
                        if (reader.Read())
                            if (reader.IsDBNull(0)) tcid = 1; else tcid = reader.GetInt32(0) + 1;
                        if (reader != null)
                        {
                            reader.Close();
                            reader.Dispose();
                        }

                        comm.CommandText = "insert into tbl_tarjetas_credito(tac_id, tac_uin_id, tac_name, tac_credit_card, tac_exp_date, tac_ccv) values (@id, @uinID, @name, @creditcard, @expdate, @ccv)";
                        comm.Parameters.Add("@id", SqlDbType.Int).Value = tcid;
                        comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                        comm.Parameters.Add("@name", SqlDbType.VarChar).Value = tarjeta.name.ToUpper();
                        comm.Parameters.Add("@creditcard", SqlDbType.VarChar).Value = tarjeta.number;
                        comm.Parameters.Add("@expdate", SqlDbType.VarChar).Value = tarjeta.expdate;
                        comm.Parameters.Add("@ccv", SqlDbType.Int).Value = tarjeta.ccv;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();

                    }
                }

            } else { // usuario existente solo hago update de los rows
                comm.CommandText = "update tbl_usuario set usu_contrasena=@pass, usu_alias=@alias, usu_nombre=@nombre, usu_apellido=@apellido, usu_compania=@compania, usu_email=@email, usu_title=@title where usu_id=@usuID";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                comm.Parameters.Add("@pass", SqlDbType.VarChar).Value = usuario.pss;
                comm.Parameters.Add("@alias", SqlDbType.VarChar).Value = usuario.alias.ToUpper();
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = usuario.nombre.ToUpper();
                comm.Parameters.Add("@apellido", SqlDbType.VarChar).Value = usuario.apellido.ToUpper();
                comm.Parameters.Add("@compania", SqlDbType.VarChar).Value = usuario.compania.ToUpper();
                comm.Parameters.Add("@email", SqlDbType.VarChar).Value = usuario.email;
                comm.Parameters.Add("@title", SqlDbType.VarChar).Value = usuario.title.ToUpper();
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "delete from tbl_usuario_perfil where upe_usu_id=@usuID and upe_per_id=@perID";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                comm.Parameters.Add("@perID", SqlDbType.VarChar).Value = int.Parse(usuario.perfil);
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                comm.CommandText = "insert into tbl_usuario_perfil (upe_usu_id, upe_per_id, upe_estado) values (@usuID, @perID, 1)";
                comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                comm.Parameters.Add("@perID", SqlDbType.VarChar).Value = int.Parse(usuario.perfil);
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();

                int uinID = 1;
                int tcid = 1;
                foreach (RE_Direccion direccion in usuario.direcciones)
                {
                    if (direccion.uinID == 0)
                    {
                        comm.CommandText = "select max(uin_id) from tbl_usuario_informacion";
                        reader = comm.ExecuteReader();
                        if (reader.Read())
                            if (reader.IsDBNull(0)) uinID = 1; else uinID = reader.GetInt32(0) + 1;
                        if (reader != null)
                        {
                            reader.Close();
                            reader.Dispose();
                        }
                        comm.CommandText = "insert into tbl_usuario_informacion(uin_id, uin_usu_id, uin_st_number, uin_st_add, uin_city, uin_state, uin_zip, uin_tel_oficina, uin_tel_celular, uin_fax, uin_tii_id) values (@uinID, @usuID, @stnum, @stadd, @city, @state, @zip, @telof, @telcel, @fax, @tipo)";
                        comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                        comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                        comm.Parameters.Add("@stnum", SqlDbType.VarChar).Value = direccion.stnumber.ToUpper();
                        comm.Parameters.Add("@stadd", SqlDbType.VarChar).Value = direccion.stadd.ToUpper();
                        comm.Parameters.Add("@city", SqlDbType.VarChar).Value = direccion.city.ToUpper();
                        comm.Parameters.Add("@state", SqlDbType.VarChar).Value = direccion.state.ToUpper();
                        comm.Parameters.Add("@zip", SqlDbType.VarChar).Value = direccion.zip;
                        comm.Parameters.Add("@telof", SqlDbType.VarChar).Value = direccion.telof;
                        comm.Parameters.Add("@telcel", SqlDbType.VarChar).Value = direccion.telcel;
                        comm.Parameters.Add("@fax", SqlDbType.VarChar).Value = direccion.fax;
                        comm.Parameters.Add("@tipo", SqlDbType.Int).Value = direccion.tipodir;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                    else
                    {
                        comm.CommandText = "update tbl_usuario_informacion set uin_usu_id=@usuID, uin_st_number=@stnum, uin_st_add=@stadd, uin_city=@city, uin_state=@state, uin_zip=@zip, uin_tel_oficina=@telof, uin_tel_celular=@@telcel, uin_fax=@fax, uin_tii_id=@tipo where uin_id=@uinID";
                        comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                        comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = usuario.userID.ToUpper();
                        comm.Parameters.Add("@stnum", SqlDbType.VarChar).Value = direccion.stnumber.ToUpper();
                        comm.Parameters.Add("@stadd", SqlDbType.VarChar).Value = direccion.stadd.ToUpper();
                        comm.Parameters.Add("@city", SqlDbType.VarChar).Value = direccion.city.ToUpper();
                        comm.Parameters.Add("@state", SqlDbType.VarChar).Value = direccion.state.ToUpper();
                        comm.Parameters.Add("@zip", SqlDbType.VarChar).Value = direccion.zip;
                        comm.Parameters.Add("@telof", SqlDbType.VarChar).Value = direccion.telof;
                        comm.Parameters.Add("@telcel", SqlDbType.VarChar).Value = direccion.telcel;
                        comm.Parameters.Add("@fax", SqlDbType.VarChar).Value = direccion.fax;
                        comm.Parameters.Add("@tipo", SqlDbType.Int).Value = direccion.tipodir;
                        comm.ExecuteNonQuery();
                        comm.Parameters.Clear();
                    }
                    foreach (RE_CreditCard tarjeta in direccion.tarjetas)
                    {
                        if (tarjeta.tacID == 0)
                        {
                            comm.CommandText = "select max(tac_id) from tbl_tarjetas_credito";
                            reader = comm.ExecuteReader();
                            if (reader.Read())
                                if (reader.IsDBNull(0)) tcid = 1; else tcid = reader.GetInt32(0) + 1;
                            if (reader != null)
                            {
                                reader.Close();
                                reader.Dispose();
                            }

                            comm.CommandText = "insert into tbl_tarjetas_credito(tac_id, tac_uin_id, tac_name, tac_credit_card, tac_exp_date, tac_ccv) values (@id, @uinID, @name, @creditcard, @expdate, @ccv)";
                            comm.Parameters.Add("@id", SqlDbType.Int).Value = tcid;
                            comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                            comm.Parameters.Add("@name", SqlDbType.VarChar).Value = tarjeta.name.ToUpper();
                            comm.Parameters.Add("@creditcard", SqlDbType.VarChar).Value = tarjeta.number;
                            comm.Parameters.Add("@expdate", SqlDbType.VarChar).Value = tarjeta.expdate;
                            comm.Parameters.Add("@ccv", SqlDbType.Int).Value = tarjeta.ccv;
                            comm.ExecuteNonQuery();
                            comm.Parameters.Clear();
                        } else {
                            comm.CommandText = "update tbl_tarjetas_credito set tac_uin_id=@uinID, tac_name=@name, tac_credit_card=@creditcard, tac_exp_date=@expdate, tac_ccv=@ccv where tac_id=@id";
                            comm.Parameters.Add("@id", SqlDbType.Int).Value = tarjeta.tacID;
                            comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
                            comm.Parameters.Add("@name", SqlDbType.VarChar).Value = tarjeta.name.ToUpper();
                            comm.Parameters.Add("@creditcard", SqlDbType.VarChar).Value = tarjeta.number;
                            comm.Parameters.Add("@expdate", SqlDbType.VarChar).Value = tarjeta.expdate;
                            comm.Parameters.Add("@ccv", SqlDbType.Int).Value = tarjeta.ccv;
                            comm.ExecuteNonQuery();
                            comm.Parameters.Clear();
                        }

                    }
                }
            }
            result = true;
            transaction.Commit();
        }
        catch (Exception e)
        {
            transaction.Rollback();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_User getUsuariosData(string userID) {
        RE_User result = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select usu_id, usu_estado, usu_fecha_creacion, usu_alias, usu_nombre, usu_apellido, usu_compania, usu_email, usu_title, usu_where, usu_where2, usu_permiso, usu_restricted  from tbl_usuario where usu_id=@usuID";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = userID.Trim().ToUpper();
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_User();
                if (!reader.IsDBNull(0)) result.userID = reader.GetString(0);
                if (!reader.IsDBNull(1)) result.estado = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) result.fechacreacion = reader.GetDateTime(2).ToShortDateString();
                if (!reader.IsDBNull(3)) result.alias = reader.GetString(3);
                if (!reader.IsDBNull(4)) result.nombre = reader.GetString(4);
                if (!reader.IsDBNull(5)) result.apellido = reader.GetString(5);
                if (!reader.IsDBNull(6)) result.compania = reader.GetString(6);
                if (!reader.IsDBNull(7)) result.email = reader.GetString(7);
                if (!reader.IsDBNull(8)) result.title = reader.GetString(8);
                if (!reader.IsDBNull(9)) result.where = reader.GetString(9);
                if (!reader.IsDBNull(10)) result.where2 = reader.GetString(10);
                if (!reader.IsDBNull(11)) result.permisouser = reader.GetInt32(11);
                if (!reader.IsDBNull(12)) result.restricted = reader.GetInt32(12);
                result.existe = 1;
                result.direcciones = GetDirecciones(userID);
                result.companies =   GetCompaniesUser(userID);
                result.accounts =    GetAccountsUser(userID);
                result.counties =    GetCountiesUser(userID);
                result.states =      GetStatesUser(userID);
                result.perfiles =    GetPerfilUser(userID);
                result.prices =      GetPricesUser(userID);
                
            }
        }
        catch (Exception e) {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetStatesUser(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT  ust_st_id FROM  tbl_usuario_state WHERE  (ust_usu_id = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value = userID.ToUpper();
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//state id

                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
   
    private static ArrayList GetCountiesUser(string userID)
    {
         ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = " SELECT ucou_county_id FROM tbl_usuario_county WHERE  (upper(ucou_usu_id) = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value= userID.ToUpper();
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//COUNTY id
              
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetPricesUser(string userID)
    {

        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT  upr_prod_id, upr_precio FROM  tbl_usuario_precio WHERE     (UPPER(upr_usu_id) = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value = userID.ToUpper();
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//product id
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetFloat(1).ToString();// precio
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetPerfilUser(string userID)
    {

        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_usuario_perfil.upe_per_id, tbl_perfil.per_nombre FROM  tbl_usuario_perfil INNER JOIN tbl_perfil ON tbl_usuario_perfil.upe_per_id = tbl_perfil.per_id WHERE   (tbl_usuario_perfil.upe_estado = 1) AND (upper(tbl_usuario_perfil.upe_usu_id) = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value = userID.ToUpper();
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = int.Parse(reader.GetString(0));//perfil id
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);// perfil nombre
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetAccountsUser(string userID)
    {
        
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_usuario_account.uacc_acc_id, tbl_account.acc_nombre, tbl_account.acc_initial FROM   tbl_usuario_account INNER JOIN  tbl_account ON tbl_usuario_account.uacc_acc_id = tbl_account.acc_id WHERE  (tbl_usuario_account.uacc_usu_id = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value= userID;
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//account id
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);// account nombre
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetCompaniesUser(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_usuario_company.uco_com_id, tbl_compania.com_name FROM tbl_usuario_company INNER JOIN   tbl_compania ON tbl_usuario_company.uco_com_id = tbl_compania.com_id WHERE     (tbl_usuario_company.uco_usu_id = @usuId)";
            comm.Parameters.Add("@usuId", SqlDbType.VarChar).Value= userID;
            reader = comm.ExecuteReader();


            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);//company id
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);//company name
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getUsuariosbyPerfil(int perfilID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_usuario.usu_id, tbl_usuario.usu_alias, tbl_usuario.usu_nombre, tbl_usuario.usu_apellido, tbl_usuario.usu_compania, tbl_usuario.usu_email,tbl_perfil.per_nombre FROM tbl_usuario INNER JOIN  tbl_usuario_perfil ON tbl_usuario.usu_id = tbl_usuario_perfil.upe_usu_id INNER JOIN tbl_perfil ON tbl_usuario_perfil.upe_per_id = tbl_perfil.per_id WHERE  (tbl_usuario_perfil.upe_per_id = @perfilID) AND (tbl_usuario.usu_estado = 1)";
            comm.Parameters.Add("@perfilID", SqlDbType.Int).Value = perfilID;
            reader = comm.ExecuteReader();

            RE_User user;
            while (reader.Read())
            {
                user = new RE_User();
                if (!reader.IsDBNull(0)) user.userID = reader.GetString(0); 
                if (!reader.IsDBNull(1)) user.alias = reader.GetString(1);
                if (!reader.IsDBNull(2)) user.nombre = reader.GetString(2);
                if (!reader.IsDBNull(3)) user.apellido = reader.GetString(3);
                if (!reader.IsDBNull(4)) user.compania = reader.GetString(4);
                if (!reader.IsDBNull(5)) user.email = reader.GetString(5);
                if (!reader.IsDBNull(6)) user.perfil = reader.GetString(6);
                result.Add(user);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getPerfiles(int catalogo)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            // si catalogo=1 significa que unicamente muestro los perfiles marcados como catalogo
            if (catalogo==1) 
                comm.CommandText = "select per_id, per_nombre, per_descripcion from tbl_perfil where per_catalogo=1 order by per_nombre";
            else
                comm.CommandText = "select per_id, per_nombre, per_descripcion from tbl_perfil where per_catalogo=0 order by per_nombre";

            reader = comm.ExecuteReader();

            RE_GenericBean2 perfil;
            while (reader.Read())
            {
                perfil = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) perfil.strC1 = reader.GetString(0);
                if (!reader.IsDBNull(1)) perfil.strC2 = reader.GetString(1);
                if (!reader.IsDBNull(2)) perfil.strC3 = reader.GetString(2);
                result.Add(perfil);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetDirecciones(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select uin_id, uin_usu_id, uin_st_number, uin_st_add, uin_city, uin_state, uin_zip, uin_tel_oficina, uin_tel_celular, uin_fax, uin_tii_id from tbl_usuario_informacion where uin_usu_id=@usuID";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = userID.Trim().ToUpper();
            reader = comm.ExecuteReader();
            RE_Direccion dir = null;
            while (reader.Read())
            {
                dir = new RE_Direccion();
                
                if (!reader.IsDBNull(0)) dir.uinID = reader.GetInt32(0);//uin_id
                //                if (!reader.IsDBNull(1)) dir.usuID=reader.getstring;//usu_id
                if (!reader.IsDBNull(2)) dir.stnumber = reader.GetString(2);//stnumber
                if (!reader.IsDBNull(3)) dir.stadd = reader.GetString(3);//stadd
                if (!reader.IsDBNull(4)) dir.city = reader.GetString(4);//city
                if (!reader.IsDBNull(5)) dir.state = reader.GetString(5);//state
                if (!reader.IsDBNull(6)) dir.zip = reader.GetString(6);//zip
                if (!reader.IsDBNull(7)) dir.telof = reader.GetString(7);//oficina
                if (!reader.IsDBNull(8)) dir.telcel = reader.GetString(8);//telcellar
                if (!reader.IsDBNull(9)) dir.fax = reader.GetString(9);//fax
                if (!reader.IsDBNull(10)) dir.tipodir = reader.GetInt32(10);//tii
                dir.tarjetas = getTarjetas(dir.uinID); // obtengo las tarjetas asociadas a esta direccion
                result.Add(dir);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList getTarjetas(int uinID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select tac_id, tac_name, tac_credit_card, tac_exp_date, tac_ccv from tbl_tarjetas_credito where tac_uin_id=@uinID";
            comm.Parameters.Add("@uinID", SqlDbType.Int).Value = uinID;
            reader = comm.ExecuteReader();
            RE_CreditCard cc = null;
            while (reader.Read())
            {
                cc=new RE_CreditCard();
                if (!reader.IsDBNull(0)) cc.tacID=reader.GetInt32(0);//tacID
                if (!reader.IsDBNull(0)) cc.name=reader.GetString(1);//name
                if (!reader.IsDBNull(0)) cc.number=reader.GetString(2);//number
                if (!reader.IsDBNull(0)) cc.expdate=reader.GetString(3);//expdate
                if (!reader.IsDBNull(0)) cc.ccv=reader.GetInt32(4);//ccv
                result.Add(cc);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static bool setLogoUser(string XMLUsuarioLogo)
    {
        bool result = false;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "update tbl_usuario set usu_logo='' where usu_id=@usuID";
         //   comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = UserID.Trim().ToUpper();
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();


        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static ArrayList getCompanies()
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select com_id, com_name from tbl_compania where com_estado= 1 ORDER BY 2";
            reader = comm.ExecuteReader();

            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }



    internal static ArrayList getAccounts()
    {
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            SqlCommand comm = null;
            SqlDataReader reader = null;
            try
            {
                conn = OpenConnection();
                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = "select acc_id, acc_nombre, acc_initial from tbl_account where acc_estado= 1 ORDER BY 2";
                reader = comm.ExecuteReader();

                RE_GenericBean2 account;
                while (reader.Read())
                {
                    account = new RE_GenericBean2();
                    if (!reader.IsDBNull(0)) account.intC1 = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) account.strC1 = reader.GetString(1);
                    if (!reader.IsDBNull(2)) account.strC2 = reader.GetString(2);
                    result.Add(account);
                }
            }
            catch (Exception e)
            {
                string ruta = "c:\\TEMP\\archivo.txt";
                StreamWriter escritor = new StreamWriter(ruta, true);
                escritor.WriteLine(e.Message.ToString());
                escritor.Close();
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                if (comm != null)
                    comm.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return result;
        }
    }

    internal static ArrayList getCompany(string comp)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select com_id, com_name from tbl_compania where com_estado= 1 and com_id="+comp+" ORDER BY 2";
            reader = comm.ExecuteReader();

            RE_GenericBean2 company;
            while (reader.Read())
            {
                company = new RE_GenericBean2();
                if (!reader.IsDBNull(0)) company.intC1 = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) company.strC1 = reader.GetString(1);
                result.Add(company);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    internal static ArrayList getAccount(string acc)
    {
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            SqlCommand comm = null;
            SqlDataReader reader = null;
            try
            {
                conn = OpenConnection();
                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = "select acc_id, acc_nombre, acc_initial from tbl_account where acc_estado= 1 and acc_id="+acc+" ORDER BY 2";
                reader = comm.ExecuteReader();

                RE_GenericBean2 account;
                while (reader.Read())
                {
                    account = new RE_GenericBean2();
                    if (!reader.IsDBNull(0)) account.intC1 = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) account.strC1 = reader.GetString(1);
                    if (!reader.IsDBNull(2)) account.strC2 = reader.GetString(2);
                    result.Add(account);
                }
            }
            catch (Exception e)
            {
                string ruta = "c:\\TEMP\\archivo.txt";
                StreamWriter escritor = new StreamWriter(ruta, true);
                escritor.WriteLine(e.Message.ToString());
                escritor.Close();
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                if (comm != null)
                    comm.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return result;
        }
    }


    internal static ArrayList getOpcionApp(int app_id)
    {//devuelve las opciones para una app en especifico
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            SqlCommand comm = null;
            SqlDataReader reader = null;
            try
            {
                conn = OpenConnection();
                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;
                comm.CommandText = "select oap_id, oap_nombre, oap_permiso from tbl_opcion_aplicacion where oap_apl_id= 1  ORDER BY oap_nombre";
                reader = comm.ExecuteReader();

                RE_GenericBean2 opcion;
                while (reader.Read())
                {
                    opcion = new RE_GenericBean2();
                    if (!reader.IsDBNull(0)) opcion.intC1 = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) opcion.strC1 = reader.GetString(1);
                    if (!reader.IsDBNull(2)) opcion.int64C1 = reader.GetInt64(2);
                    result.Add(opcion);
                }
            }
            catch (Exception e)
            {
                string ruta = "c:\\TEMP\\archivo.txt";
                StreamWriter escritor = new StreamWriter(ruta, true);
                escritor.WriteLine(e.Message.ToString());
                escritor.Close();
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                if (comm != null)
                    comm.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return result;
        }
    }

    internal static bool InsertarCompany(RE_GenericBean2 company)
    {
         bool result = false;
        
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
        
        try
        {


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
        
            if (company!= null)
            {
                comm.CommandText = "insert into tbl_compania ( com_name, com_estado) values (@comName,@estado)";
                comm.Parameters.Add("@comName", SqlDbType.VarChar).Value = company.strC1;
                comm.Parameters.Add("@estado", SqlDbType.Int).Value = company.intC1;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                return true;

             }
        }
        catch (Exception e)
        {
            
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }
    internal static bool InsertarAccount(RE_GenericBean2 company)
    {
        bool result = false;

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();

        try
        {


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            if (company != null)
            {
                comm.CommandText = "insert into tbl_account ( acc_nombre, acc_initial, acc_estado) values (@accName, @accinitial,@accestado)";
                comm.Parameters.Add("@accName", SqlDbType.VarChar).Value = company.strC1;
                comm.Parameters.Add("@accinitial" ,SqlDbType.VarChar).Value = company.strC2;
                comm.Parameters.Add("@accestado", SqlDbType.Int).Value = company.intC1;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                return true;

            }
        }
        catch (Exception e)
        {

            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }

    internal static bool InsertarTEmployee(RE_GenericBean2 temployee)
    {
        bool result = false;

        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        conn = OpenConnection();
      
        try
        {


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
          
            int next_per_id=0;
            if (temployee != null)
            {
                comm.CommandText = "SELECT TOP (1) CAST(per_id AS int) AS perfil_id FROM  tbl_perfil ORDER BY perfil_id DESC";
                reader = comm.ExecuteReader();

                
                while (reader.Read())
                {
                    
                    if (!reader.IsDBNull(0)) next_per_id = reader.GetInt32(0);
                    next_per_id = next_per_id + 1;
                    
                }
                reader.Close();
                reader.Dispose();
                if (next_per_id != 0)
                {

                    comm.CommandText = "insert into tbl_perfil (per_id, per_nombre, per_catalogo) values (@perid,@pernombre, @percatalogo)";
                    comm.Parameters.Add("@perid", SqlDbType.Int).Value = next_per_id;
                    comm.Parameters.Add("@pernombre", SqlDbType.VarChar).Value = temployee.strC1;
                    comm.Parameters.Add("@percatalogo", SqlDbType.Int).Value = temployee.intC1;
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                    
                    return true;

                }
            }
        }
        catch (Exception e)
        {

            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString()+" "+e.StackTrace+" "+comm.CommandText);
            escritor.Close();
            return false;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;

    }

    internal static int delete(string tablename, string criterio, string valorcriterio, int intvalorcriterio, int type)
    {//borrar los registros de la tabla tablename  segun el criterio con el valor que recibe STRING ==1 int==0
         int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete from "+tablename+"  where "+criterio+" =@criterio";
            if (type==1){
                comm.Parameters.Add("@criterio", SqlDbType.VarChar).Value = valorcriterio;

            }
            if (type == 0)
            {
                comm.Parameters.Add("@criterio", SqlDbType.Int).Value = intvalorcriterio;

            }

            comm.ExecuteNonQuery();
            result = 1;
        }
        catch (Exception ex) {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setPerfilUsuario(string usuarioid, int[] matperfil, int count,int estado)
    {
       int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++ )
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_perfil (upe_usu_id, upe_per_id, upe_estado) values (@usuid,@perfil,@estado)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@perfil", SqlDbType.Int).Value = matperfil[i];
                comm.Parameters.Add("@estado", SqlDbType.Int).Value = estado;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setCompanyporUsuario(string usuarioid, int[] matcompany, int count)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_company (uco_usu_id, uco_com_id) values (@usuid,@company)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@company", SqlDbType.Int).Value = matcompany[i];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
               
            }
            result = 1;
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int updatedata(string tablename, string columnanme,  string criterio, string valorcriterio, int valor, string valorst,int tipo)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

           
                comm.CommandType = CommandType.Text;
                comm.CommandText = "update tbl_usuario set "+columnanme+"= @valor where "+criterio+"= @valorcriterio";
                if(tipo==1){ 
                    comm.Parameters.Add("@valor", SqlDbType.VarChar).Value = valorst;
                }
                if(tipo==0){
                    comm.Parameters.Add("@valor", SqlDbType.Int).Value = valor;
                }
                comm.Parameters.Add("@valorcriterio", SqlDbType.VarChar).Value = valorcriterio;
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                result = 1;
              
        }
             
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setStateporUsuario(string usuarioid, int[] matstate, int count)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_state (ust_usu_id, ust_st_id) values (@usuid,@state)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@state", SqlDbType.Int).Value = matstate[i];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
               
            }
            result = 1;
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setPrecioporUsuario(string usuarioid, string[][] matriz, int count)
    {

        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_precio (upr_usu_id, upr_prod_id, upr_precio) values (@usuid,@product,@price)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@product", SqlDbType.Int).Value = matriz[i][0];
                comm.Parameters.Add("@price", SqlDbType.Real).Value = matriz[i][1];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
                
            }
            result = 1;
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int setCountyporUsuario(string usuarioid, int[] matriz, int count)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_county (ucou_usu_id, ucou_county_id) values (@usuid,@county)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@county", SqlDbType.Int).Value = matriz[i];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
            result = 1;
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }



    private static ArrayList GetCompanies(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_compania.com_name, tbl_compania.com_id FROM  tbl_compania INNER JOIN  tbl_usuario_company ON tbl_compania.com_id = tbl_usuario_company.uco_com_id WHERE     (tbl_usuario_company.uco_usu_id = 'LUCIANAB')";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = userID.Trim().ToUpper();
            reader = comm.ExecuteReader();
            RE_GenericBean2 Companies = null;
            while (reader.Read())
            {
                Companies = new RE_GenericBean2();

                if (!reader.IsDBNull(0)) Companies.strC1 = reader.GetString(0);//company name
                if (!reader.IsDBNull(1)) Companies.intC1 = reader.GetInt32(1);//company id
                result.Add(Companies);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    private static ArrayList GetAccounts(string userID)
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tbl_compania.com_name, tbl_compania.com_id FROM  tbl_compania INNER JOIN  tbl_usuario_company ON tbl_compania.com_id = tbl_usuario_company.uco_com_id WHERE     (tbl_usuario_company.uco_usu_id = 'LUCIANAB')";
            comm.Parameters.Add("@usuID", SqlDbType.VarChar).Value = userID.Trim().ToUpper();
            reader = comm.ExecuteReader();
            RE_GenericBean2 Companies = null;
            while (reader.Read())
            {
                Companies = new RE_GenericBean2();

                if (!reader.IsDBNull(0)) Companies.strC1 = reader.GetString(0);//company name
                if (!reader.IsDBNull(1)) Companies.intC1 = reader.GetInt32(1);//company id
                result.Add(Companies);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    internal static int  setAccountporUsuario(string usuarioid, int[] matcompany, int count)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;

            for (int i = 0; i < count; i++)
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = "insert into tbl_usuario_account (uacc_usu_id, uacc_acc_id) values (@usuid,@acc)";
                comm.Parameters.Add("@usuid", SqlDbType.VarChar).Value = usuarioid;
                comm.Parameters.Add("@acc", SqlDbType.Int).Value = matcompany[i];
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
        }
        catch (Exception ex)
        {
            result = 0;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
}
